﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:SYS_PRODUCT
	/// </summary>
	public partial class SYS_PRODUCT : BaseDAL<GSLMS.Entity.TB_SYS_PRODUCT>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public SYS_PRODUCT()
		{}
		#region  Method
		/// <summary>
		///  增加一条数据
		/// </summary>
		public override bool Add(GSLMS.Entity.TB_SYS_PRODUCT model)
		{
			bool flag = false;
            int num = AddProduct(model);
            if (num == 1)
                flag = true;
			
			return flag;
		}

        /// <summary>
        /// 新增产品信息，-1产品编号已经存在，-2产品名称已经存在，1成功，0失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddProduct(GSLMS.Entity.TB_SYS_PRODUCT model)
        {
            int num = 0;
            OracleParameter[] parms = new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PRODUCTNO", model.PRODUCTNO),
								new OracleParameter("P_PRODUCTNAME", model.PRODUCTNAME),
								new OracleParameter("P_INTCYCLE", model.INTCYCLE),
								new OracleParameter("P_RATE", model.RATE),
								new OracleParameter("P_MANAGERATE", model.MANAGERATE),
								new OracleParameter("P_FEERATE", model.FEERATE),
								new OracleParameter("P_PAYMENT", model.PAYMENT),
								new OracleParameter("P_PARM1", model.PARM1),
								new OracleParameter("P_PARM2", model.PARM2),
								new OracleParameter("P_ISHOLIDAY", model.ISHOLIDAY),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID),
								new OracleParameter("P_SALESRPTID", model.SALESRPTID),
								new OracleParameter("P_RISKRPTID", model.RISKRPTID),
								new OracleParameter("P_METINFOSETID", model.METINFOSETID),
								new OracleParameter("P_STATUS", model.STATUS),
								new OracleParameter("P_REMARK", model.REMARK),
                                new OracleParameter("returnvalue",OracleType.Int32),
                                new OracleParameter("p_createby",model.CREATEBY),
                                new OracleParameter("p_createdate",model.CREATEDATE),
                                new OracleParameter("p_modifyby",model.MODIFYBY),
                                new OracleParameter("p_modifydate",model.MODIFYDATE)
                        };

            for (int i = 0; i < 17; i++)
            {
                parms[i].Direction = ParameterDirection.Input;
            }
            parms[17].Direction = ParameterDirection.Output;

            parms[18].Direction = ParameterDirection.Input;
            parms[19].Direction = ParameterDirection.Input;
            parms[20].Direction = ParameterDirection.Input;
            parms[21].Direction = ParameterDirection.Input;

            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_product_add", parms);
                if (parms[17].Value != DBNull.Value)
                    num = OracleHelper.ToInt(parms[17].Value.ToString());

                if (num == 1)
                {
                    //清除缓存
                    OracleHelper.RemoveCacheObject("_ProductInfoList");
                }
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }
            return num;
        }

		/// <summary>
		///  更新一条数据
		/// </summary>
		public override bool Update(GSLMS.Entity.TB_SYS_PRODUCT model)
		{
			bool flag = false;
            int num = UpdateProduct(model);
            if (num == 1)
                flag = true;


			return flag;
		}

        /// <summary>
        /// 编辑产品信息，-1产品编号已经存在，-2产品名称已经存在，1成功，0失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateProduct(GSLMS.Entity.TB_SYS_PRODUCT model)
        {
            int num = 0;
            OracleParameter[] parms = new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PRODUCTNO", model.PRODUCTNO),
								new OracleParameter("P_PRODUCTNAME", model.PRODUCTNAME),
								new OracleParameter("P_INTCYCLE", model.INTCYCLE),
								new OracleParameter("P_RATE", model.RATE),
								new OracleParameter("P_MANAGERATE", model.MANAGERATE),
								new OracleParameter("P_FEERATE", model.FEERATE),
								new OracleParameter("P_PAYMENT", model.PAYMENT),
								new OracleParameter("P_PARM1", model.PARM1),
								new OracleParameter("P_PARM2", model.PARM2),
								new OracleParameter("P_ISHOLIDAY", model.ISHOLIDAY),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID),
								new OracleParameter("P_SALESRPTID", model.SALESRPTID),
								new OracleParameter("P_RISKRPTID", model.RISKRPTID),
								new OracleParameter("P_METINFOSETID", model.METINFOSETID),
								new OracleParameter("P_STATUS", model.STATUS),
								new OracleParameter("P_REMARK", model.REMARK),
                                new OracleParameter("returnvalue",OracleType.Int32),
                                new OracleParameter("p_createby",model.CREATEBY),
                                new OracleParameter("p_createdate",model.CREATEDATE),
                                new OracleParameter("p_modifyby",model.MODIFYBY),
                                new OracleParameter("p_modifydate",model.MODIFYDATE)
                 };

            for (int i = 0; i < 17; i++)
            {
                parms[i].Direction = ParameterDirection.Input;
            }

            parms[17].Direction = ParameterDirection.Output;
            parms[18].Direction = ParameterDirection.Input;
            parms[19].Direction = ParameterDirection.Input;
            parms[20].Direction = ParameterDirection.Input;
            parms[21].Direction = ParameterDirection.Input;

            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_product_update", parms);
                if (parms[17].Value != DBNull.Value)
                    num = OracleHelper.ToInt(parms[17].Value.ToString());

                if (num == 1)
                {
                    //清除缓存
                    OracleHelper.RemoveCacheObject("_ProductInfoList");
                }
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }
            return num;
        }

		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", ID)};
  
				parms[0].Direction = ParameterDirection.Input;

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_product_delete", parms);
				flag = true;
                //清除缓存
                OracleHelper.RemoveCacheObject("_ProductInfoList");
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_SYS_PRODUCT DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_SYS_PRODUCT model = new GSLMS.Entity.TB_SYS_PRODUCT();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["PRODUCTNO"]!=null && dr["PRODUCTNO"].ToString()!="")
				{
					model.PRODUCTNO=dr["PRODUCTNO"].ToString();
				}
				if(dr["PRODUCTNAME"]!=null && dr["PRODUCTNAME"].ToString()!="")
				{
					model.PRODUCTNAME=dr["PRODUCTNAME"].ToString();
				}
				if(dr["INTCYCLE"]!=null && dr["INTCYCLE"].ToString()!="")
				{
                    model.INTCYCLE = OracleHelper.ToInt(dr["INTCYCLE"].ToString());
				}
				if(dr["RATE"]!=null && dr["RATE"].ToString()!="")
				{
					model.RATE=OracleHelper.ToDecimal(dr["RATE"].ToString());
				}
				if(dr["MANAGERATE"]!=null && dr["MANAGERATE"].ToString()!="")
				{
					model.MANAGERATE=OracleHelper.ToDecimal(dr["MANAGERATE"].ToString());
				}
				if(dr["FEERATE"]!=null && dr["FEERATE"].ToString()!="")
				{
					model.FEERATE=OracleHelper.ToDecimal(dr["FEERATE"].ToString());
				}
				if(dr["PAYMENT"]!=null && dr["PAYMENT"].ToString()!="")
				{
                    model.PAYMENT = OracleHelper.ToInt(dr["PAYMENT"].ToString());
				}
				if(dr["PARM1"]!=null && dr["PARM1"].ToString()!="")
				{
                    model.PARM1 = OracleHelper.ToInt(dr["PARM1"].ToString());
				}
				if(dr["PARM2"]!=null && dr["PARM2"].ToString()!="")
				{
                    model.PARM2 = OracleHelper.ToInt(dr["PARM2"].ToString());
				}
				if(dr["ISHOLIDAY"]!=null && dr["ISHOLIDAY"].ToString()!="")
				{
                    model.ISHOLIDAY = OracleHelper.ToInt(dr["ISHOLIDAY"].ToString());
				}
				if(dr["SYSTEMID"]!=null && dr["SYSTEMID"].ToString()!="")
				{
                    model.SYSTEMID = OracleHelper.ToInt(dr["SYSTEMID"].ToString());
				}
				if(dr["SALESRPTID"]!=null && dr["SALESRPTID"].ToString()!="")
				{
					model.SALESRPTID=OracleHelper.ToLong(dr["SALESRPTID"].ToString());
				}
				if(dr["RISKRPTID"]!=null && dr["RISKRPTID"].ToString()!="")
				{
					model.RISKRPTID=OracleHelper.ToLong(dr["RISKRPTID"].ToString());
				}
				if(dr["METINFOSETID"]!=null && dr["METINFOSETID"].ToString()!="")
				{
					model.METINFOSETID=OracleHelper.ToLong(dr["METINFOSETID"].ToString());
				}
				if(dr["STATUS"]!=null && dr["STATUS"].ToString()!="")
				{
                    model.STATUS = OracleHelper.ToInt(dr["STATUS"].ToString());
				}
				if(dr["REMARK"]!=null && dr["REMARK"].ToString()!="")
				{
					model.REMARK=dr["REMARK"].ToString();
				}
				return model;
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_SYS_PRODUCT GetByID(long ID)
		{
			GSLMS.Entity.TB_SYS_PRODUCT sModel = new GSLMS.Entity.TB_SYS_PRODUCT();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " id="+ID.ToString(), "");
			if (dt!=null&&dt.Rows.Count>0)
			{
				sModel = DataRowToModel(dt.Rows[0]);
			}
			else
			{
			  return null;
			}
			return sModel;
		}

        /// <summary>
        /// 根据系统提取产品信息
        /// </summary>
        /// <param name="systemID">系统类别</param>
        /// <param name="status">状态，-1为所有，1有效，0无效</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_PRODUCT> GetBySysetmID(GSLMS.Entity.Enum.SystemEnum.SystemID systemID,int status)
        {
            DataTable dt = null;
            var list = new List<GSLMS.Entity.TB_SYS_PRODUCT>();

            string sqlWhere = "";

            sqlWhere = " systemid=" + ((int)systemID).ToString();
            if (status > -1)
                sqlWhere += " and status=" + status.ToString();

            dt = OracleHelper.DataTableFileter(GetAllByCache(),sqlWhere, "");

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_PRODUCT model = new GSLMS.Entity.TB_SYS_PRODUCT();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }

            return list;
        }






        /// <summary>
        /// 从缓存中获取产品信息
        /// </summary>
        /// <returns></returns>
        private DataTable GetAllByCache()
        {
            DataTable dt = null;
            if (OracleHelper.GetCachecObject("_ProductInfoList") != null)
                dt = (DataTable)OracleHelper.GetCachecObject("_ProductInfoList");
            if (dt == null || dt.Rows.Count == 0)
            {
                string sql = "";
                sql = "select * from tb_sys_product order by id desc";
                DataSet ds = OracleHelper.ExecuteDataset(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    OracleHelper.AddCacheObject("_ProductInfoList", (object)dt, 0);
                    //清除缓存
                    //OracleHelper.RemoveCacheObject("_ProductInfoList");
                }

            }

            return dt;
        }
             

		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_SYS_PRODUCT> GetList(GSLMS.Entity.TB_SYS_PRODUCT searchParam)
		{
			var list=new List<GSLMS.Entity.TB_SYS_PRODUCT>();
			DataTable dt = null;
			string tblName = "TB_SYS_PRODUCT";
			string sqlWhere="";
			string strSort="";
			int pageSize=20;
			int pageIndex=1;
			string strFields="*";
			int totalCount = 0;
			int countFlag = 1;

            //是否计算总数
            countFlag = (searchParam.ReCount != null && searchParam.ReCount) ? 1 : 0;
            pageSize = (searchParam.PageSize != null && searchParam.PageSize > 0) ? searchParam.PageSize : 15;
            pageIndex = (searchParam.CurrentIndex != null && searchParam.CurrentIndex > 0) ? searchParam.CurrentIndex : 1;


                  
              //产品编号：
            if (searchParam.PRODUCTNO != null && searchParam.PRODUCTNO.Trim() != "")
                sqlWhere += " and PRODUCTNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.PRODUCTNO) + "%'";
              
              //产品名称：
            if (searchParam.PRODUCTNAME != null && searchParam.PRODUCTNAME.Trim() != "")
                sqlWhere += " and PRODUCTNAME like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.PRODUCTNAME) + "%'";
             
              
             
              //所属系统：
             if (searchParam.SYSTEMID != null && searchParam.SYSTEMID > 0)
                sqlWhere += " and SYSTEMID=" + searchParam.SYSTEMID.ToString();
             
              //状态：
             if (searchParam.STATUS != null && searchParam.STATUS > 0)
                 sqlWhere += " and STATUS=" + searchParam.STATUS.ToString();
              
              
			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);

            searchParam.RowCount = totalCount;
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_SYS_PRODUCT model = new GSLMS.Entity.TB_SYS_PRODUCT();
					model = DataRowToModel(dt.Rows[i]);
					list.Add(model);
				}
			}
			
			return list;
		}

		#endregion  Method
	}
}

